package DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBspel {
	private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";
    private static String tableName = "spel";
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();
        createStructure();
        shutdown();
    }
    
    public static void createStructure(){
    	try{
	    	stmt = conn.createStatement();
	    	try{
	    		stmt.executeUpdate( "DROP TABLE " + tableName );
	    		stmt.executeUpdate( "DROP TABLE score ");
	    		
	    	} catch ( Exception e ){
	    		System.out.println( "An error has occured while creating database, please contact the administrator" );
	    	}
    	
    	
	       stmt.executeUpdate("CREATE TABLE spel " +
	        		"( id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, " +
	        		" naam VARCHAR(50) ," +
	        		" email VARCHAR(50) ," +
	        		" map INTEGER ," +
	        		" score INTEGER )");
	        try{
	        stmt.executeUpdate("CREATE TABLE score " +
	        		"( id INTEGER NOT NULL, " +
	        		" lvl INTEGER ," +
	        		" tijd INTEGER ," +
	        		" aantalTorens INTEGER ," +
	        		" basisHealth INTEGER ," +
	        		" scorePunten INTEGER ," +
	        		" geld INTEGER ," +
	        		" kills INTEGER )");

	       // stmt.executeUpdate( "ALTER TABLE spel " + 
	       //"ADD FOREIGN KEY (id) REFERENCES score(id);");
	        stmt.close();
	        }
	        catch( Exception e ){
	        	System.out.println( "An error has occured while creating database, please contact the administrator" );
	        }
    	} catch ( Exception e ){
    		System.out.println( "An error has occured while creating database, please contact the administrator" );
    	}
    }
    
    public static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL); 
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }
    
    public static String[][] getBest( int map ){
    	String arr[][] = new String[10][4];
    	
    	try {
    		stmt = conn.createStatement();
    		ResultSet results = stmt.executeQuery( "SELECT naam, email, map, score FROM spel " + 
    				"WHERE map = " + map + " ORDER BY score DESC" );
    		int i = 0;
    		while(results.next() && i < 10)
            {
    			arr[i][0] = "" + results.getString(1);
        		arr[i][1] = "" + results.getString(2);
        		arr[i][2] = "" + results.getInt(3);
        		arr[i][3] = "" + results.getInt(4);
        		i++;
            }
    		
        	return arr;
    	} catch ( Exception e ){
    		System.out.println( "Error while fetching data best player" );
    		e.printStackTrace();
    		return null;	
    	}
    	
    }
    
    
    public static int getBestScore( int map ){
    	try {
    		stmt = conn.createStatement();
    		ResultSet results = stmt.executeQuery( "SELECT score FROM spel " + 
    				"WHERE map = " + map + " AND score IN ( " + 
    				"SELECT max( score ) FROM spel GROUP BY map )" );
    		int retVal =0;
    		while(results.next())
            {
        		retVal = results.getInt(1);
            }
        	return retVal;
    	} catch ( Exception e ){
    		System.out.println( "Error while fetching data best player" );
    		e.printStackTrace();
    		return 0;	
    	}
    	
    }
    
    public static void select(){
    	try {
    		stmt = conn.createStatement();
    		ResultSet results = stmt.executeQuery("select * from " + tableName);
    		while(results.next())
            {
                int id = results.getInt(1);
                String naam = results.getString(2);
                String email = results.getString(3);
                String map = results.getString(4);
                String score = results.getString(5);
                System.out.println( id + "\t" + naam + "\t\t\t" + email + "\t" + map + "\t" + score );
            }
            results.close();
            
            results = stmt.executeQuery("SELECT * FROM score ");
            

    		while(results.next())
            {
                int id = results.getInt(1);
                int lvl = results.getInt(2);
                int tijd = results.getInt(3);
                int aantalTorens = results.getInt(4);
                int basisHealth = results.getInt(5);
                int scorePunten = results.getInt(6);
                int geld = results.getInt(7);
                int kills  = results.getInt(8);
                System.out.println( id + "\t" + lvl + "\t\t\t" + tijd + "\t" + aantalTorens + "\t\t" + basisHealth + "\t" + scorePunten + "\t" + geld + "\t" + kills );
            }
            results.close();
            
            stmt.close();
    	} catch ( Exception e ){}
        
    }
    
    public static void insertIntoSpel( String naam, String email , int map, int score )
    {
        try
        {
            stmt = conn.createStatement();
            stmt.execute("INSERT INTO " + tableName + " (naam, email, map, score ) " + 
            		"VALUES ( '" + naam + "','" + email + "'," + map + "," + score + ")");
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            System.out.println( "An error has occured while inserting into spel" );
        }
    }
    
    public static void insertIntoScore( int level, int tijd, int aantalTorens, int basisHealth, int scorePunten, int geld, int kills ){
    	try{
    		stmt = conn.createStatement();
//    		maken dat hij automatisch het id fetcht
    		int id = 0;
    		ResultSet results = stmt.executeQuery( "SELECT max(id) FROM spel" );
    		while(results.next())
            {
                id = results.getInt(1);
            }
            stmt.execute("INSERT INTO score ( id, lvl, tijd, aantalTorens, basisHealth, scorePunten, geld, kills ) " + 
            		"VALUES ( " + id + ", " + level + "," + tijd + "," + aantalTorens + "," + basisHealth + "," + scorePunten + "," + geld + "," + kills + ")");
            stmt.close();
    	} catch ( Exception e ){
    		System.out.println( "An error has occured while inserting into scores" );
    		e.printStackTrace();
    	}
    }
    
    public static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }           
        }
        catch (SQLException sqlExcept){}
    }
    
}